/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.querie.collab;

import com.querie.Algos.SeparateSimilarity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;
import java.util.Collections;

import com.querie.db.QueryFragmentFactory;
import com.querie.db.DBMSConnection;
import com.querie.beans.DatabaseAccess;
import com.querie.pojo.QueryItemResult;
import com.querie.pojo.Accuracy;
import com.querie.parser.exception.*;
import com.querie.pojo.RecommendedQuery;
import com.querie.util.QryParameters;

/**
 *
 * @author a-skoshy
 */
public abstract class ItemSimilarity {

    protected Connection con = null;

    public ItemSimilarity() throws SQLException {
        con = DBMSConnection.getConnection();

    }

    /* Backend process for finding the item to item similarity for all items in the system.
     * Initiates the query fragmenting and then similarity calculations
     */
    public void processBackendForAllSessions(String sessions) throws Exception {
        QryParameters qparams = QryParameters.getInstance();
        qparams.setValue("TRAINFRAGSTOPTIME", "0");
        qparams.setSpecialValue("TRAINFRAGSTARTTTIME", "now()");
        QueryFragmentFactory factory = new QueryFragmentFactory();
        factory.setFilter(sessions);
        factory.processQueriesFromDB(); //for now no threading
        qparams.setSpecialValue("TRAINFRAGSTOPTIME", "now()");
        String categorized = qparams.getValue("SEPARATE");
        boolean isCategorized = false;
        if (categorized.equals("no")) {
            isCategorized = false;
        } else {
            isCategorized = true;
        }
        calculateAllItemSimilarity(isCategorized);
        //selectKSimItem();
        close();
    }

    /* Backend process for finding the item to item similarity for all items in the system.
     * Calls the stored procedure and writes results to ItemSimilarity table
     */
    public abstract void calculateAllItemSimilarity(boolean isCategorzied) throws SQLException;


    /* Find the similarity between the current session and provides recommendations for the current session.
     * Steps are
    1.The items are inserted for the current query
     * 2. Top predicted items are found
     * 3. Find the recommendations
     * 4. The results are packaged in a POJO and returned
     *
     *
     */
    public QueryItemResult findSimilarity(int sessionID, String query) throws Exception {
        SeparateSimilarity sm=new SeparateSimilarity();
        int queryID = insertQuery(sessionID, query);
        

        String activeQry = null;
        List<String> activeSessionQueries = new ArrayList<String>();
        List<String> activeSessionFragments = new ArrayList<String>();
        List<RecommendedQuery> simQueries = null;
        //System.out.println("findSimilarity " + queryID);
        QueryFragmentFactory factory = new QueryFragmentFactory();

        String sql = "Select queryid,queryString from sessionqueries where sessionid = ? order by queryid";
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        pstmt.setInt(1, sessionID);
        rs = pstmt.executeQuery();

        while (rs.next()) {
            queryID = rs.getInt(1);

            activeQry = rs.getString(2);
            //System.out.println("Query for test" + activeQry);
            factory.processQueriesForSession(queryID, con);

            activeSessionQueries.add(activeQry);
            activeSessionFragments.add(findfragmentForQuery(queryID));

        }
        //Query qry = factory.processQueriesForSession(queryID, con);
        String topPredList = findTopPredictedItems(sessionID);
        String topPredItemNames = findTopPredictedItemsNames(topPredList);

        int numrecItems = getCountOfTopPredItems(topPredList);
        if (numrecItems != 0) {
            //float maxval = maxMatchForQuery(topPredList, numrecItems,sessionID);
            simQueries = getSimilarQueries(topPredList, 0.0f, numrecItems, sessionID);
            //simQueries = getSimilarQueries(topPredList, maxval, numrecItems, sessionID);
            //for (RecommendedQuery simQuery : simQueries) {
            //    System.out.println(simQuery);
            //}
        }

        QueryItemResult result = new QueryItemResult();
        result.setSimQueries(simQueries);
        result.setSessionID(sessionID);
        System.out.println(topPredList.replace(',', '|'));
        result.setTopPredictedItems(topPredList.replace(',', '|'));
        result.setActiveSessionQueries(activeSessionQueries);
        result.setActiveSessionFragments(activeSessionFragments);
        result.setTopPredictedItemNames(topPredItemNames.replace(',', '|'));
        sm.SessionSimilarity(sessionID);
        //result.setQueryObject(qry);
        return result;

    }

    public List<QueryItemResult> findAllTestSetSimilarity() throws Exception {
        QueryItemResult qry = null;
        QueryFragmentFactory factory = new QueryFragmentFactory();
        factory.cleanForTest();
        List<QueryItemResult> results = new ArrayList<QueryItemResult>();
        QryParameters qParam = QryParameters.getInstance();
        String testString = qParam.getValue("TESTSESSION");
        String[] testSessions = testString.split(",");
        for (int i = 0; i < testSessions.length; i++) {
            qry = findTestSimilarity(Integer.parseInt(testSessions[i]));
            results.add(qry);
        }
        return results;
    }

    /* This method is used to find the similarites for the test set. The same step as the findSimilarity is follwed here
     * Except that the last query is not considered as it is considered unseen data and treated likewise
     * 
     *
     */
    public QueryItemResult findTestSimilarity(int sessionID) throws Exception {
      SeparateSimilarity sm=new SeparateSimilarity();
        try {
            int queryID = 0;
            int holdQueryID = 0;
            String holdOutQry = null;
            String testQry = null;
            String testQryList = "";

            QryParameters qparams = QryParameters.getInstance();
            String numQueries = qparams.getValue("NUMTESTQUERYINSESSION");
            //int numTestQ = Integer.parseInt(numQueries);

            List<String> testSessionQueries = new ArrayList<String>();
            List<String> holdOutQueries = new ArrayList<String>();
            List<String> holdOutStringQueries = new ArrayList<String>();
            List<RecommendedQuery> simQueries = null;
            QueryFragmentFactory factory = new QueryFragmentFactory();

            String sql = "select queryString,queryid from sessionqueries where " +
                    "  sessionid = ? order by queryid limit " + numQueries;
            System.out.println(sql);
            PreparedStatement pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, sessionID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                queryID = rs.getInt(2);
                testQryList = testQryList + queryID + ",";
                testQry = rs.getString(1);
                //System.out.println("Query for test" + testQry);
                factory.processQueriesForSession(queryID, con);
                testSessionQueries.add(testQry);

            }
            testQryList = testQryList.substring(0, testQryList.length() - 1);

            DBMSConnection.safeClose(rs, pstmt, null);

            sql = "Select queryid, queryString from sessionqueries where sessionid = ? and queryid not in ( " + testQryList + ")";
            pstmt = con.prepareStatement(sql);
            rs = null;
            pstmt.setInt(1, sessionID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                holdQueryID = rs.getInt(1);
                holdOutQry = rs.getString(2);
                holdOutQueries.add("" + holdQueryID);
                holdOutStringQueries.add(holdOutQry);
            }


            DBMSConnection.safeClose(rs, pstmt, null);

            String topPredList = findTopPredictedItems(sessionID);
            String topPredItemNames = findTopPredictedItemsNames(topPredList);
            int numrecItems = getCountOfTopPredItems(topPredList);
            if (numrecItems != 0) {
                //float maxval = maxMatchForQuery(topPredList, numrecItems);
                //simQueries = getSimilarQueries(topPredList, maxval, numrecItems);
                simQueries = getSimilarQueries(topPredList, 0.0f, numrecItems, sessionID);
                for (RecommendedQuery simQuery : simQueries) {
                    System.out.println(simQuery);
                }


            }

            for (int h = 0; h < holdOutQueries.size(); h++) {
                holdQueryID = Integer.parseInt(holdOutQueries.get(h));
                factory.processQueriesForSession(holdQueryID, con);
            }
            if (simQueries != null) {
                simQueries = getAccuracy(holdOutQueries, simQueries);
            }

            QueryItemResult result = new QueryItemResult();
            result.setSimQueries(simQueries);
            result.setTopPredictedItems(topPredList.replace(',', '|'));
            result.setTopPredictedItemNames(topPredItemNames.replace(',', '|'));
            result.setHoldOutStringQueries(holdOutStringQueries);
            result.setSessionID(sessionID);
            result.setActiveSessionQueries(testSessionQueries);
            sm.SessionSimilarity(sessionID);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
              
    }

  

    /* The list string is separated by the "," and the number of items is counted
    by finding the number of items between the commas */
    private int getCountOfTopPredItems(String list) {

        String[] items = list.split(",");
        if (items[0].trim().equals("")) {
            return 0;
        } else {
            return items.length;
        }

    }

    private List<RecommendedQuery> getAccuracy(List<String> holdOutQueries, List<RecommendedQuery> simQueries) throws SQLException {
        Accuracy acc = null;
        RecommendedQuery rq = null;
        List<RecommendedQuery> copyQueries = new ArrayList<RecommendedQuery>();
        List<Accuracy> accList = null;
        int comItemsCount = 0;
        int holdMaxItemsCount = 0;
        float precision;
        float recall;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int holdOutQueryID = 0;
        float[] highAcc = null;
        Accuracy acchig;


        String sql1 = "select count(*) from qryitems \n" +
                " where queryid = ? ";
        String sql = "select count(distinct hld.itemid) from qryitems hld, qryitems rq \n" +
                        " where hld.itemid = rq.itemid and \n" +
                        " hld.queryid = ? and rq.queryid = ? ";



        for (int i = 0; i < simQueries.size(); i++) {
            rq = simQueries.get(i);
            accList = new ArrayList<Accuracy>();

            for (int h = 0; h < holdOutQueries.size(); h++) {
                holdOutQueryID = Integer.parseInt(holdOutQueries.get(h));
                pstmt = con.prepareStatement(sql1);
                pstmt.setInt(1, holdOutQueryID);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    holdMaxItemsCount = rs.getInt(1);
                }
                DBMSConnection.safeClose(rs, pstmt, null);
               
                pstmt = con.prepareStatement(sql);
                pstmt.setInt(1, holdOutQueryID);
                pstmt.setInt(2, rq.getQueryID());
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    comItemsCount = rs.getInt(1);

                }
                precision = (float) ((float) comItemsCount / (float) rq.getNumItemsinQry());
                recall = (float) ((float) comItemsCount / (float) holdMaxItemsCount);

                //System.out.println(holdOutQueryID + " " + rq.getQueryID() + " " + comItemsCount + ":" + holdMaxItemsCount + ":" + rq.getNumItemsinQry() + ":" + precision + ":" + recall);

                acc = new Accuracy(precision,recall);
                accList.add(acc);
                
            }
            Collections.sort(accList);
            highAcc = new float[2];
            if(accList.size()!=0){
            acchig = accList.get(accList.size()-1);
            highAcc[0] = acchig.getPrecision();
            highAcc[1] = acchig.getRecall();
            rq.setAccuracy(highAcc);
            }else{

            rq.setAccuracy(null);
            }
            copyQueries.add(rq);
        }

        return copyQueries;
    }

   
    /*
     *
     * Find all items in the current session
     * Find prediction for each item
     * Find the highest n prediction

     *
     */
    public abstract String findTopPredictedItems(int sessionID) throws Exception;

    /* Refer DatabaseAccess.findSimilarQueries  for precision & recall */
    public float maxMatchForQuery(String itemList, int numPredItems, int sessionid) throws Exception {
        float maxCount;
        System.out.println("maxMatchForQuery " + itemList);
        /*String sql = " select max(items) from " +
        " (select q.queryid, count(*) items " +
        " from qryitems q, sessionqueries s " +
        " where q.queryid= s.queryid  " +
        " and q.itemid in (" + itemList + ") " +
        " group by q.queryid) collab";*/

        String sql = " select truncate(max((predCount*predCount)/(maxcount * " + numPredItems + ")),2) \n" +
                "from \n" +
                "    ( \n" +
                "        select queryid, count(*) predCount \n" +
                "        from qryitems \n" +
                "        where itemid in (" + itemList + ") \n" +
                "        group by queryid \n" +
                "    ) predItemQry, \n" +
                "    ( \n" +
                "        select queryid, count(*) maxCount \n" +
                "        from qryitems \n" +
                "        where queryid in \n" +
                "        ( \n" +
                "            select q.queryid \n" +
                "            from qryitems q \n" +
                "            where q.itemid in (" + itemList + ") \n" +
                "        ) " +
                "        group by queryid \n" +
                "    )maxItemQry \n" +
                "where maxItemQry.queryid = predItemQry.queryid and s.sessionid <> " + sessionid;

        //System.out.println("maxMatchForQuery " + sql);

        Statement stmt = con.createStatement();
        ResultSet rs = null;

        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            maxCount = rs.getFloat(1);
        } else {
            throw new QuerySystemException("Something is seriously wrong");
        }
        return maxCount;
    }

    /* * Find the queries associated with the prediction
    and the one with the highest count is taken as recommendation
     *
     */
    public List<RecommendedQuery> getSimilarQueries(String itemList, float maxNormVal, int numPredItems, int sessionid) throws Exception {
        System.out.println("getSimilarQueries " + itemList + " " + maxNormVal);
        List<RecommendedQuery> similarQueries = new ArrayList<RecommendedQuery>();
        RecommendedQuery rq = null;
        int maxQ = 0;
        /*String sql = " select s.queryString " +
        " from qryitems q, sessionqueries s " +
        " where q.queryid= s.queryid  " +
        " and q.itemid in (" + itemList + ") " +
        " group by q.queryid " +
        " having count(*) = " + maxCnt;*/

        QryParameters qParam = QryParameters.getInstance();
        String topq = qParam.getValue("TOPQ");
        String removeSame = qParam.getValue("REMOVESAMEREC");
        int itopQ = Integer.parseInt(topq);
        int multi = 20;
        if(itopQ >= 10) multi = 2;
        if (removeSame.equals("yes")) {
            maxQ = itopQ * multi;
        } else {
            maxQ = itopQ;
        }

        String sql = " select s.queryString,predCount, maxCount, s.queryid, s.sessionid " +
                "from " +
                "    ( " +
                "        select queryid, count(*) predCount " +
                "        from qryitems " +
                "        where itemid in (" + itemList + ") " +
                "        group by queryid " +
                "    ) predItemQry, " +
                "    ( " +
                "        select queryid, count(*) maxCount " +
                "        from qryitems \n" +
                "        where queryid in " +
                "        ( " +
                "            select q.queryid " +
                "            from qryitems q " +
                "            where q.itemid in (" + itemList + ") " +
                "        ) " +
                "        group by queryid " +
                "    )maxItemQry, sessionqueries s " +
                "where maxItemQry.queryid = predItemQry.queryid " +
                "and maxItemQry.queryid = s.queryid and s.sessionid <> " + sessionid +
                " order by (predCount*predCount)/(maxcount * " + numPredItems + ") desc limit " + (maxQ);
        //"and (predCount * predCount)/(maxcount * " + numPredItems + ")>= " + maxNormVal;

        //System.out.println("getSimilarQueries " + sql);
        Statement stmt = con.createStatement();
        ResultSet rs = null;

        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            rq = new RecommendedQuery();
            rq.setQuery(rs.getString(1));
            rq.setNumItemsFromRecommendedItems(rs.getInt(2));
            rq.setNumItemsinQry(rs.getInt(3));
            rq.setQueryID(rs.getInt(4));
            rq.setSessionID(rs.getInt(5));
            similarQueries.add(rq);
        }

        //System.out.println("All Similar Queries");
        //for (RecommendedQuery simQuery : similarQueries) {
        //            System.out.println(simQuery);
        //        }

        if (removeSame.equals("yes")) {
            similarQueries = getdifferentQueries(similarQueries, itopQ);
        }

        DBMSConnection.safeClose(rs, stmt, null);
        return similarQueries;
    }


    /* Checking each query to see whether its items are same as previous
     *
     *
     *
     */
    public List<RecommendedQuery> getdifferentQueries(List<RecommendedQuery> simiQueries, int topQ) throws Exception {
        List<RecommendedQuery> diffQueries = new ArrayList<RecommendedQuery>();
        RecommendedQuery rq = null;
        int curQID = 0;
        int selQID = 0;
        int sameCnt = 0;
        int maxCnt = 0;
        boolean isSame = false;
        int totCnt = 1;

        if (simiQueries.size() == 0) {
            return diffQueries;
        }
        diffQueries.add(simiQueries.get(0));

        String sql = "select * from( \n" +
                " (select  count(*) sameCount from qryitems \n" +
                " where itemid in (select itemid from qryitems where queryid= ?) \n" +
                " and queryid = ?) sameItems,  \n" +
                " (select queryid, count(*) maxCount         from qryitems \n" +
                " where queryid = ?) totitems)";
        //System.out.println("getdifferentQueries " + sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;

        for (int i = 1; i < simiQueries.size(); i++) {
            rq = simiQueries.get(i);
            curQID = rq.getQueryID();
            isSame = false;
            for (int j = 0; j < diffQueries.size(); j++) {
                selQID = diffQueries.get(j).getQueryID();
                //System.out.println(selQID + " " + curQID);
                pstmt.setInt(1, selQID);
                pstmt.setInt(2, curQID);
                pstmt.setInt(3, curQID);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    sameCnt = rs.getInt(1);
                    maxCnt = rs.getInt(3);
                }
                DBMSConnection.safeClose(rs, null, null);
                if (sameCnt == maxCnt) {
                    isSame = true;
                    break;

                }

            }
            if (!isSame) {
                diffQueries.add(rq);
                totCnt++;
                if (totCnt == topQ) {
                    break;
                }

            }


        }

        DBMSConnection.safeClose(null, pstmt, null);

        return diffQueries;


    }

    /* Out of all the similarities calculated for all items, choosing the top k items
     * The k value is a configuration and can be modified
     */
    public void selectKSimItem() throws Exception {
        QueryFragmentFactory factory = new QueryFragmentFactory();
        List<String> itemList = new ArrayList<String>();
        factory.cleanForTopK();
        Connection connection = DBMSConnection.getConnection();
        String kvalue = com.querie.util.QryParameters.getInstance().getValue("KVALUE");

        int k = 0;
        try {
            k = Integer.parseInt(kvalue);
        } catch (NumberFormatException num) {
            k = 5;
            num.printStackTrace();
        }

        int check = 500/k;
       
        String simiSQL = "select item2 from itemsimilarity " +
                "where item1=? order by simivalue desc limit " + k;
        ResultSet rSet1 = null;
        String itemid = null;
        String listStr = "";
        String sql = "insert into TOPKITEMSIMILARITY (item1, item2,simivalue)" +
                " select item1, item2, simivalue from itemsimilarity " +
                " where item2 in (";
        String insertSQL = "";
        
        Statement insertStmt = null;

        Statement stmt = connection.createStatement();
        String sQuery = "select ITEMID from collabITEMS ";
        ResultSet rSet = stmt.executeQuery(sQuery);
        int counter = 0;

        while (rSet.next()) {
            itemid = rSet.getString(1);
            itemList.add(itemid);
        }
        DBMSConnection.safeClose(rSet, stmt, connection);

         Connection connection1 = DBMSConnection.getConnection();
        connection = DBMSConnection.getConnection();
        connection.setAutoCommit(false);
         PreparedStatement pstmt = null;
                 //connection1.prepareStatement("select item2 from itemsimilarity " +
                //"where item1=? order by simivalue desc limit " + k);

        for(int i=0; i < itemList.size(); i++){
            if (counter == 0) {
                insertStmt = connection.createStatement();
                pstmt = connection1.prepareStatement(simiSQL);
            }
            itemid = itemList.get(i);
            pstmt.setString(1, itemid);
            rSet1 = pstmt.executeQuery();
            listStr = "";
            insertSQL = "";
            while (rSet1.next()) {
                listStr = listStr + "'" + rSet1.getString(1) + "',";
            }
            if (listStr.equals("")) {
                continue;
            }
            listStr = listStr.substring(0, listStr.length() - 1);
            insertSQL = sql + listStr + ") and item1 = '" + itemid + "'";

            System.out.println("insertSQL  " + counter + " " + insertSQL);
            insertStmt.addBatch(insertSQL);
            counter++;
            if (counter == check) {
                insertStmt.executeBatch();
                connection.commit();
                counter = 0;
                DBMSConnection.safeClose(null, insertStmt, null);
                DBMSConnection.safeClose(null, pstmt, null);
            }
            //insertStmt.close();
            DBMSConnection.safeClose(rSet1, null, null);
            //Thread.sleep(100);



        }
        if (counter != 0) {
            insertStmt.executeBatch();
            connection.commit();
            counter = 0;
        }
        DBMSConnection.safeClose(null, insertStmt, null);
        DBMSConnection.safeClose(null, pstmt, connection);
         DBMSConnection.safeClose(null, null, connection1);
        

    }

    /* Method for inserting a new Query into database based on user's current session
     *
     */
    public int insertQuery(int sessionID, String query) throws Exception {
        DatabaseAccess databaseAccess = DatabaseAccess.getInstance();
        int queryId = databaseAccess.generateNewQueryId();
        PreparedStatement stmt = con.prepareStatement(
                "insert into sessionqueries(sessionId, queryString, queryId) values(?, ?, ?)");

        stmt.setInt(1, sessionID);
        stmt.setString(2, query);
        stmt.setInt(3, queryId);
        stmt.executeUpdate();

        stmt.close();
        return queryId;
    }

    /*
     *
     * Find all names of the items from the item id list - supporting function to give more details
     *
     */
    public String findTopPredictedItemsNames(String itemList) throws Exception {
        System.out.println("findTopPredictedItemsNames " + itemList);

        String listStr = "";
        if(!itemList.equals(""))
        {
            String sql ="select c.* from collabitems c where itemid in (" + itemList + ")";
        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
    //    String listStr = "";
        String itemName = "";
        String isTable = "";
        String isColumn = "";
        String isWhere = "";
        String comments = "";

        String realName = "";

        rs = pstmt.executeQuery();
        listStr = "";
        while (rs.next()) {
            itemName = rs.getString(2);
            isTable = rs.getString(3);
            isColumn = rs.getString(4);
            isWhere = rs.getString(5);
            comments = rs.getString(7);

            if(isTable == null) isTable = "";
            if(isColumn == null) isColumn = "";
            if(isWhere == null) isWhere = "";

            realName = decipherItemsNames(itemName,isTable,isColumn,isWhere,comments);
            
            System.out.println("Item Name:" + itemName + " Real Name:" + realName);

            listStr = listStr + realName + ",";
        }
       
        
        if (!listStr.equals("")) {
            listStr = listStr.substring(0, listStr.length() - 1);
        }

        DBMSConnection.safeClose(rs, pstmt, null);
         }//endif
        else
                  listStr = "";
        
        return listStr;
    }

     /*
     *
     * Find all names of the items from the item id list - supporting function to give more details
     *
     */
    public String findfragmentForQuery(int qryID) throws Exception {
        System.out.println("findfragmentForQuery " + qryID);

        String sql ="select  c.* from sessionqueries a,qryitems b, collabitems c " +
                " where a.queryid =? " +
                   " and a.queryid=b.queryid and b.itemid=c.itemid ";
        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        String listStr = "";
        String itemName = "";
        String isTable = "";
        String isColumn = "";
        String isWhere = "";
        String comments = "";

        String realName = "";
        pstmt.setInt(1, qryID);
        rs = pstmt.executeQuery();
        listStr = "";
        while (rs.next()) {
            itemName = rs.getString(2);
            isTable = rs.getString(3);
            isColumn = rs.getString(4);
            isWhere = rs.getString(5);
            comments = rs.getString(7);

            if(isTable == null) isTable = "";
            if(isColumn == null) isColumn = "";
            if(isWhere == null) isWhere = "";

            realName = decipherItemsNames(itemName,isTable,isColumn,isWhere,comments);

            System.out.println("Item Name:" + itemName + " Real Name:" + realName);

            listStr = listStr + realName + ",";
        }
        if (!listStr.equals("")) {
            listStr = listStr.substring(0, listStr.length() - 1);
        }

        DBMSConnection.safeClose(rs, pstmt, null);
        return listStr;
    }

    public String decipherItemsNames(String itemName, String isTable, String isColumn,
            String isWhere, String comments) throws Exception {
        String cname = null;
        if(isTable.equals("Y")){
            return comments;
        }else if(isColumn.equals("Y")){

            if(itemName.endsWith(".*")){ // This is already deciphered and stored in collab tiems as table.*
                return itemName;
            }else if(itemName.startsWith("C")){
                cname = getColumnNameFromTargetTable(itemName);
                if(cname != null)
                    return cname;
                else
                    return comments;
            }
            
        }else if(isWhere.equals("Y")){
           return decipherWhereClause(itemName);

        }
            return itemName;
        

        
    }

    public String decipherWhereClause(String where) throws Exception{
        String[] colNames = where.split("\\s+");
        String returnStr = "";
        String part = "";
        String onecolString;
        for (int i = 0; i < colNames.length; i++) {
            part = "";
            onecolString = colNames[i].toUpperCase().trim();
            if(onecolString.trim().equals("")){
                continue;
            }else if(onecolString.startsWith("C")){
               part = getColumnNameFromTargetTable(onecolString);
               if (part == null) part = onecolString;
            } else {
                part = onecolString.trim();
            }
            returnStr = returnStr + " " + part;
         }
        return returnStr;
    }

    public String getColumnNameFromTargetTable(String columnID) throws Exception{
        String sql ="select tablename,columnname from targetcolumns c,targettables t" +
                    " where c.tableid = t.tableid and columnid = ?";
        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        String tableid = null;
        String cname = null;

        pstmt.setString(1, columnID);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            tableid = rs.getString(1);
            cname = rs.getString(2);

            System.out.println("Column Name:" +tableid + "." + cname );

            
        }
        DBMSConnection.safeClose(rs, pstmt, null);
        if (tableid != null) return tableid + "." + cname;
        else
        return null;
    }

    public void close() throws SQLException {
        DBMSConnection.safeClose(null, null, con);
    }
}
